﻿/* modify by xlli 090707
 * description:
 * 在系统运行中，有时候会有多个线程同时执行，但是由于编码的原因导致多个线程共享一个OraAgent实例，
 * 所以在并发情况下，有可能导致数据库连接关闭的异常。为了解决这个问题，在执行数据库操作时，
 * 为每个实例加lock。
 * 加锁不是一个好的办法，但是由于涉及代码太多，所以只好选择这个方法。
 * */
#region Using directives

using System;
using System.Text;
using System.Data.OracleClient;
using System.Configuration;
using System.Data;
//using Metarnet.MVC.Framework;
#endregion

namespace MetarCommonSupport
{
    public class OraAgent : IDBAgent,System.IDisposable 
    {
        private OracleConnection m_aConn;
        private OracleCommand m_aCmd;
        private OracleDataAdapter m_aAdpt;

		private OracleTransaction m_aTran;
		private bool m_OpenTransaction;

        private string m_conStr ;
        
        public OraAgent(string strCon)
        {
           this.m_conStr = strCon;
           this.Init ();
        }

		private void Init()
		{
			if(this.m_aConn !=null )
			{
				this.m_aConn.Dispose ();
				this.m_aConn = null;
			}
			if(this.m_aCmd !=null )
			{
				this.m_aCmd .Dispose ();
				this.m_aCmd = null;
			}
			if(this.m_aAdpt !=null )
			{
				this.m_aAdpt .Dispose ();
				this.m_aAdpt = null;
			}
			if(this.m_aTran !=null )
			{
				this.m_aTran .Dispose ();
				this.m_aTran = null;
			}

			this.m_aConn = new OracleConnection();
			this.m_aCmd = new OracleCommand();
			this.m_aAdpt = new OracleDataAdapter();

			m_aConn.ConnectionString = m_conStr;
			this.m_aCmd.Connection = this.m_aConn;
			this.m_aAdpt.SelectCommand = this.m_aCmd;

			this.m_OpenTransaction = false ;
		}

		//Modify by xhliu 060513 
		public int ExeNonQuery(string strSql)
		{
			int nReturn = 0 ;
            lock (this)
            {
                try
                {
                    this.m_aCmd.CommandText = strSql;
                    if (this.m_aConn.State == ConnectionState.Closed)
                        this.m_aConn.Open();
                    if (this.m_OpenTransaction)
                        this.m_aCmd.Transaction = this.m_aTran;
                    nReturn = this.m_aCmd.ExecuteNonQuery();
                    if (!this.m_OpenTransaction)
                        this.m_aConn.Close();

                }
                catch (System.Data.OracleClient.OracleException oraEx)
                {
                    if (this.m_aConn.State == ConnectionState.Open)
                        this.m_aConn.Close();
                    if (this.m_OpenTransaction == true)
                    {
                        this.m_OpenTransaction = false;
                    }
                    else if (this.IsNeedReconnect(oraEx) == true)
                    {
                        this.Reconnect();
                        this.m_aCmd.CommandText = strSql;
                        if (this.m_aConn.State == ConnectionState.Closed)
                            this.m_aConn.Open();
                        nReturn = this.m_aCmd.ExecuteNonQuery();
                        return nReturn;
                    }
                    throw;

                }
                catch (Exception ex)
                {
                    System.Console.WriteLine(ex.Message);
                    System.Console.WriteLine(ex.StackTrace);
                    if (this.m_aConn.State == ConnectionState.Open && !this.m_OpenTransaction)
                        this.m_aConn.Close();
                    throw;
                }
            }
			return nReturn;
		}

        public int ExeProcedure(string strProdureName, string[] strParam, string[] strParamValue)
        {
            int nReturn = 0;
            lock (this)
            {
                CommandType ctTemp = this.m_aCmd.CommandType;
                try
                {
                    this.m_aCmd.CommandText = strProdureName;
                    this.m_aCmd.CommandType = CommandType.StoredProcedure;
                    for (int i = 0; i < strParam.Length; i++)
                    {
                        OracleParameter oraPara = new OracleParameter(strParam[i], strParamValue[i]);
                        oraPara.Direction = ParameterDirection.Input;
                        this.m_aCmd.Parameters.Add(oraPara);
                    }
                    if (this.m_aConn.State == ConnectionState.Closed)
                        this.m_aConn.Open();
                    if (this.m_OpenTransaction)
                        this.m_aCmd.Transaction = this.m_aTran;
                    nReturn = this.m_aCmd.ExecuteNonQuery();
                    if (!this.m_OpenTransaction)
                        this.m_aConn.Close();
                    this.m_aCmd.CommandType = ctTemp;
                }
                catch (System.Data.OracleClient.OracleException oraEx)
                {
                    if (this.m_aConn.State == ConnectionState.Open)
                        this.m_aConn.Close();
                    if (this.m_OpenTransaction == true)
                    {
                        this.m_OpenTransaction = false;
                    }
                    else if (this.IsNeedReconnect(oraEx) == true)
                    {
                        this.Reconnect();
                        this.m_aCmd.CommandText = strProdureName;
                        if (this.m_aConn.State == ConnectionState.Closed)
                            this.m_aConn.Open();
                        nReturn = this.m_aCmd.ExecuteNonQuery();
                        return nReturn;
                    }
                    throw;

                }
                catch (Exception ex)
                {
                    System.Console.WriteLine(ex.Message);
                    System.Console.WriteLine(ex.StackTrace);
                    if (this.m_aConn.State == ConnectionState.Open && !this.m_OpenTransaction)
                        this.m_aConn.Close();
                    throw;
                }
                finally
                {
                    this.m_aCmd.Parameters.Clear();
                    this.m_aCmd.CommandType = ctTemp;
                }
            }
            return nReturn;
        }
        public OracleDataReader ExeQueryDataReader(string strSql)
        {
            this.m_aCmd.CommandText = strSql;
            OracleDataReader dr ;
            lock (this)
            {
                try
                {
                    dr = this.m_aCmd.ExecuteReader();
                }
                catch (System.Data.OracleClient.OracleException oraEx)
                {
                    if (this.m_aConn.State == ConnectionState.Open)
                        this.m_aConn.Close();
                    if (this.m_OpenTransaction == true)
                    {
                        this.m_OpenTransaction = false;
                    }
                    else if (this.IsNeedReconnect(oraEx) == true)
                    {
                        this.Reconnect();
                        this.m_aCmd.CommandText = strSql;
                        dr = this.m_aCmd.ExecuteReader();
                        return dr;
                    }
                    throw;

                }
                catch (Exception ex)
                {
                    System.Diagnostics.Debug.Write(ex.ToString());
                    if (this.m_aConn.State == ConnectionState.Open && !this.m_OpenTransaction)
                        this.m_aConn.Close();
                    throw;
                }
            }
            return dr;
        }
        /// <summary>
        /// 存储二进制文件
        /// </summary>
        /// <param name="buf">文件缓存</param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public bool ExeNonQuery(byte[] buf,string sql)
        {
            lock (this)
            {
                try
                {
                    this.m_aTran = this.m_aConn.BeginTransaction();
                    this.m_aCmd.CommandText = sql;
                    this.m_aCmd.Transaction = this.m_aTran;

                    OracleDataReader dr;
                    dr = this.m_aCmd.ExecuteReader();
                    dr.Read();

                    OracleLob lob = dr.GetOracleLob(0);
                    lob.Erase();
                    lob.Write(buf, 0, buf.Length);
                    this.m_aTran.Commit();
                }
                catch (Exception ex)
                {
                    System.Diagnostics.Debug.Write(ex.ToString());
                    return false;
                }
            }
            return true;
        }
        public DataTable ExeQuery(string strSql)
        {
            DateTime dt = DateTime.Now;
            this.m_aCmd.CommandText = strSql;
            DataTable dtResult = new DataTable();
            lock (this)
            {
                try
                {
                    if (this.m_aConn.State != ConnectionState.Open)
                    {
                        this.m_aConn.Close();

                        this.m_aConn.Open();
                        this.m_aAdpt.Fill(dtResult);
                        this.m_aConn.Close();
                    }
                    else
                    {
                        this.m_aAdpt.Fill(dtResult);
                    }
                }
                catch (System.Data.OracleClient.OracleException oraEx)
                {
                    FrmErrShow.ShowErrMessage(strSql);
                    if (this.m_aConn.State == ConnectionState.Open)
                        this.m_aConn.Close();
                    if (this.m_OpenTransaction == true)
                    {
                        this.m_OpenTransaction = false;
                    }
                    else if (this.IsNeedReconnect(oraEx) == true)
                    {
                        this.Reconnect();
                        this.m_aCmd.CommandText = strSql;
                        this.m_aAdpt.Fill(dtResult);
                        return dtResult;
                    }
                    throw;
                }
                catch (Exception ex)
                {
                    System.Console.WriteLine(ex.Message);
                }
            }
            TimeSpan ts = DateTime.Now - dt;
            //if (ts.TotalSeconds > 1)
            //    System.Windows.Forms.MessageBox.Show("Test");
            return dtResult;
        }

        public object ExecuteScalar(string strSql)
        {
            this.m_aCmd.CommandText = strSql;
            object returnObj=null;
            lock (this)
            {
                try
                {
                    if (this.m_aConn.State != ConnectionState.Open)
                    {
                        this.m_aConn.Close();

                        this.m_aConn.Open();
                        returnObj=m_aCmd.ExecuteScalar();
                        this.m_aConn.Close();
                    }
                    else
                    {
                        returnObj = m_aCmd.ExecuteScalar();
                    }
                }
                catch (System.Data.OracleClient.OracleException oraEx)
                {
                    FrmErrShow.ShowErrMessage(strSql);
                    if (this.m_aConn.State == ConnectionState.Open)
                        this.m_aConn.Close();
                    if (this.m_OpenTransaction == true)
                    {
                        this.m_OpenTransaction = false;
                    }
                    else if (this.IsNeedReconnect(oraEx) == true)
                    {
                        this.Reconnect();
                        this.m_aCmd.CommandText = strSql;
                        returnObj = m_aCmd.ExecuteScalar();
                        return returnObj;
                    }
                    throw;
                }
                catch (Exception ex)
                {
                    System.Console.WriteLine(ex.Message);
                }
            }
            return returnObj;
        }

		public DataSet ExeQuery(string strSql,int start,int length,string table)
		{
			strSql = string.Format ("select * from ({0}) where rownum <={1}",
				strSql,
				(start + length).ToString ());
			DataSet ds=new DataSet();
            lock (this)
            {
                try
                {
                    this.m_aCmd.CommandText = strSql;
                    this.m_aAdpt.Fill(ds, start, length, table);
                }
                catch (System.Data.OracleClient.OracleException oraEx)
                {
                    if (this.m_aConn.State == ConnectionState.Open)
                        this.m_aConn.Close();
                    if (this.m_OpenTransaction == true)
                    {
                        this.m_OpenTransaction = false;
                    }
                    else if (this.IsNeedReconnect(oraEx) == true)
                    {
                        this.Reconnect();
                        this.m_aCmd.CommandText = strSql;
                        this.m_aAdpt.Fill(ds, start, length, table);
                        return ds;
                    }
                    throw;
                }
                catch (Exception ex)
                {
                    System.Console.WriteLine(ex.Message);

                    string strErrorMsg = "执行SQL语句出错！" + Environment.NewLine;
                    strErrorMsg += "具体错误信息：" + ex.Message + Environment.NewLine;
                    strErrorMsg += "SQL语句：" + Environment.NewLine + strSql;
                    throw new Exception(strErrorMsg);
                }
            }
			return ds;

		}

        public DataTable ExeQuery(string strSql, string[] strPara, string[] strParaValue)
        {
            this.m_aCmd.CommandText = strSql;
            DataTable dtResult = new DataTable();
            lock (this)
            {
                for (int i = 0; i < strPara.Length; i++)
                {
                    OracleParameter oraPara = new OracleParameter(strPara[i], strParaValue[i]);
                    this.m_aCmd.Parameters.Add(oraPara);
                }
                try
                {
                    this.m_aAdpt.Fill(dtResult);
                }
                catch (System.Data.OracleClient.OracleException oraEx)
                {
                    if (this.m_aConn.State == ConnectionState.Open)
                        this.m_aConn.Close();
                    if (this.m_OpenTransaction == true)
                    {
                        this.m_OpenTransaction = false;
                    }
                    else if (this.IsNeedReconnect(oraEx) == true)
                    {
                        this.Reconnect();
                        this.m_aCmd.CommandText = strSql;
                        this.m_aAdpt.Fill(dtResult);
                        return dtResult;
                    }
                    throw;
                }
                catch (Exception ex)
                {
                    System.Console.WriteLine(ex.Message);

                    string strErrorMsg = "执行SQL语句出错！" + Environment.NewLine;
                    strErrorMsg += "具体错误信息：" + ex.Message + Environment.NewLine;
                    strErrorMsg += "SQL语句：" + Environment.NewLine + strSql;
                    throw new Exception(strErrorMsg);
                }
                finally
                {
                    this.m_aCmd.Parameters.Clear();
                }
            }
            return dtResult;
        }

		/// <summary>
		/// Create by xhliu 060513 
		/// Begins a database transaction.
		/// </summary>
		public void BeginTransaction()
		{
            lock (this)
            {
                if (this.m_aConn.State != ConnectionState.Open)
                {
                    this.m_aConn.Open();
                }
                this.m_aTran = this.m_aConn.BeginTransaction();
                this.m_OpenTransaction = true;
            }

		}

		/// <summary>
		/// Create by xhliu 060513 
		/// Commits the database transaction.
		/// </summary>
		/// <remarks>
		/// Will close the connection.
		/// </remarks>
		public void CommitTransaction()
		{
            lock (this)
            {
                this.m_aTran.Commit();
                this.m_aTran.Dispose();
                this.m_aTran = null;
                if (this.m_aConn.State != ConnectionState.Closed)
                {
                    this.m_aConn.Close();
                }
                this.m_OpenTransaction = false;
            }
		}

		/// <summary>
		/// Create by xhliu 060513 
		/// Rolls back a transaction from a pending state.
		/// </summary>
		/// <remarks>
		/// Will close the connection.
		/// </remarks>
		public void RollBackTransaction()
		{
            lock (this)
            {
                try
                {
                    this.m_aTran.Rollback();
                }
                catch (Exception ex)
                {
                    System.Console.WriteLine(ex.Message);
                }
                this.m_aTran.Dispose();
                this.m_aTran = null;
                if (this.m_aConn.State != ConnectionState.Closed)
                {
                    this.m_aConn.Close();
                }
                this.m_OpenTransaction = false;
            }
		}
        public bool IsNeedReconnect(System.Data.OracleClient.OracleException oraEx)
        {
            if (oraEx.Code == 3114 || oraEx.Code == 12571)
            {
                return true;
            }
            return false;
        }

        public void Reconnect()
        {
            //清空连接池
            System.Data.OracleClient.OracleConnection.ClearPool(this.m_aConn);
            //重新创建连接以及连接池
            this.Init();
        }
	

		/// <summary>
		/// Create by xhliu 060513 
		/// Releasing, or resetting resources.
		/// </summary>
		public void Dispose()
		{
			if(this.m_aConn !=null )
			{
				if ( !this.m_OpenTransaction )
				{
                    //if (this.m_aConn .State != ConnectionState.Closed)
                    //{
                    //    this.m_aConn.Close ();
                    //}
				}
				else
				{
					if (this.m_aConn.State != ConnectionState.Closed)
					{
						this.RollBackTransaction();
					}
				}
			}
			
			if(this.m_aAdpt !=null )
			{
				this.m_aAdpt .Dispose ();
				this.m_aAdpt = null;
			}
			if(this.m_aTran !=null )
			{
				this.m_aTran .Dispose ();
				this.m_aTran = null;
			}
			if(this.m_aCmd !=null )
			{
				this.m_aCmd .Dispose ();
				this.m_aCmd = null;
			}
            //if(this.m_aConn !=null )
            //{
            //    this.m_aConn.Dispose ();
            //    this.m_aConn = null;
            //}
		}

        #region IDBAgent 成员


        public object ExeStoredProcedure(string StoreName, string[] strParam, string[] strParamValue, int[] strParamType)
        {
            lock (this)
            {
                try
                {
                    if (this.m_aConn.State != ConnectionState.Open)
                    {
                        this.m_aConn.Close();
                        this.m_aConn.Open();
                        return ExeStoredProce(StoreName, strParam, strParamValue, strParamType);
                    }
                    else
                    {
                        return ExeStoredProce(StoreName, strParam, strParamValue, strParamType);
                    }
                }
                catch (System.Data.OracleClient.OracleException oraEx)
                {
                    FrmErrShow.ShowErrMessage(StoreName);
                    if (this.m_aConn.State == ConnectionState.Open)
                        this.m_aConn.Close();
                    if (this.m_OpenTransaction == true)
                    {
                        this.m_OpenTransaction = false;
                    }
                    else if (this.IsNeedReconnect(oraEx) == true)
                    {
                        this.Reconnect();
                    }
                    throw;
                }
                catch (Exception ex)
                {
                    System.Console.WriteLine(ex.Message);
                }
            }
            return null;
        }

        private object ExeStoredProce(string StoreName, string[] strParam, string[] strParamValue, int[] strParamType)
        {
            CommandType ocmdType = this.m_aCmd.CommandType;
            try
            {
                this.m_aCmd.CommandType = CommandType.StoredProcedure;
                m_aCmd.Parameters.Clear();
                m_aCmd.CommandText = StoreName;
                for (int i = 0; i < strParam.Length; i++)
                {
                    OracleParameter oraPara = new OracleParameter(strParam[i], strParamValue[i]);
                    oraPara.Size = 300;
                    if (strParamType[i] == 0)
                        oraPara.Direction = ParameterDirection.Input;
                    else
                        oraPara.Direction = ParameterDirection.Output;
                    this.m_aCmd.Parameters.Add(oraPara);
                }
                m_aCmd.ExecuteNonQuery();

                string retValue = m_aCmd.Parameters[m_aCmd.Parameters.Count - 1].Value.ToString();
                return m_aCmd.Parameters[m_aCmd.Parameters.Count - 1].Value;
            }
            catch (Exception ex)
            {
                throw new BaseException(this, ex.Message, ex);
            }
            finally
            {
                this.m_aCmd.Parameters.Clear();
                this.m_aCmd.CommandType = ocmdType;
                this.m_aConn.Close();
            }
        }


        #endregion
    }
}
